package br.com.sga.modelo.dao;

import br.com.sga.util.conexao.Pool;
import br.com.sga.erro.ConexaoException;
import br.com.sga.erro.DaoException;
import br.com.sga.modelo.entidade.Orgao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 *
 * @author kenzo
 */
public class OrgaoDaoImpl implements OrgaoDao{
    private Pool pool;
    String sqlInserir = "INSERT INTO orgaos (nome,codigo,sigla,administracao,status,id_orgao_pai) VALUES(?,?,?,?,?,?)";
    String sqlAtualizar = "UPDATE orgaos SET nome=?,codigo=?,sigla=?,administracao=?,status=?,id_orgao_pai=? WHERE (id_orgao=?)";
    String sqlExcluir = "DELETE FROM orgaos WHERE (id_orgao=?)";
    String sqlConsultar = "SELECT id_orgao,nome,status,administracao,codigo,id_orgao_pai,sigla FROM orgaos WHERE id_orgao=?";
    String sqlListar = "SELECT id_orgao,nome,status,administracao,codigo,id_orgao_pai,sigla FROM orgaos";
    
    public OrgaoDaoImpl(Pool pool){
        this.pool = pool;
    }

    @Override
    public void salvar(Orgao bean) throws ConexaoException, DaoException{
        Connection c = pool.getConnection();
        PreparedStatement pst;
        try{
            pst = c.prepareStatement(sqlInserir);
            
            pst.setString(1, bean.getNome());
            pst.setString(2, bean.getCodigo());
            pst.setString(3, bean.getSigla());
            pst.setString(4, bean.getAdministracao());
            pst.setInt(5, bean.getStatus());
            if( bean.getIdOrgaoPai()!=null )
                pst.setInt(6, bean.getIdOrgaoPai());
            else
                pst.setNull(6, 0);
            pst.executeUpdate();

            pst.close();
        }catch(SQLException e){
            throw new DaoException(e);
        }finally{
            pool.freeConnection(c);
        }
        
    }

    @Override
    public void atualizar(Orgao bean) throws ConexaoException, DaoException {
        Connection c = pool.getConnection();
        PreparedStatement pst;
        try{
            pst = c.prepareStatement(sqlAtualizar);
            
            pst.setString(1, bean.getNome());
            pst.setString(2, bean.getCodigo());
            pst.setString(3, bean.getSigla());
            pst.setString(4, bean.getAdministracao());
            pst.setInt(5, bean.getStatus());
            pst.setInt(6, bean.getIdOrgaoPai());
            pst.setInt(7, bean.getId());
            pst.executeUpdate();

            pst.close();
        }catch(SQLException e){
            throw new DaoException(e);
        }finally{
            pool.freeConnection(c);
        }
    }

    @Override
    public void excluir(Integer id) throws ConexaoException, DaoException {
        Connection c = pool.getConnection();
        PreparedStatement pst;
        try{
            pst = c.prepareStatement(sqlExcluir);
            
            pst.setInt(1, id);
            pst.executeUpdate();

            pst.close();
        }catch(SQLException e){
            throw new DaoException(e);
        }finally{
            pool.freeConnection(c);
        }
    }

    @Override
    public Orgao getBean(Integer id) throws ConexaoException, DaoException {
        Orgao o = null;
        Connection c = pool.getConnection();
        PreparedStatement pst;
        try{
            pst = c.prepareStatement(sqlConsultar);
            pst.setInt(1, id);
            ResultSet rs = pst.executeQuery();
            
            if( rs.next() ){
                o = new Orgao();
                o.setId( rs.getInt("id_orgao") );
                o.setNome( rs.getString("nome") );
                o.setStatus( rs.getInt("status") );
                o.setAdministracao( rs.getString("administracao") );
                o.setCodigo( rs.getString("codigo") );
                o.setIdOrgaoPai( rs.getInt("id_orgao_pai") );
                o.setSigla( rs.getString("sigla") );
            }

            pst.close();
            return o;
        }catch(SQLException e){
            throw new DaoException(e);
        }finally{
            pool.freeConnection(c);
        }
    }

    @Override
    public List<Orgao> getBeans() throws ConexaoException, DaoException {
        Orgao o = null;
        List<Orgao> lista = new ArrayList<>();
        Connection c = pool.getConnection();
        PreparedStatement pst;
        try{
            pst = c.prepareStatement(sqlListar);
            ResultSet rs = pst.executeQuery();
            
            while( rs.next() ){
                o = new Orgao();
                o.setId( rs.getInt("id_orgao") );
                o.setNome( rs.getString("nome") );
                o.setStatus( rs.getInt("status") );
                o.setAdministracao( rs.getString("administracao") );
                o.setCodigo( rs.getString("codigo") );
                o.setIdOrgaoPai( rs.getInt("id_orgao_pai") );
                o.setSigla( rs.getString("sigla") );
                lista.add(o);
            }

            pst.close();
            return lista;
        }catch(SQLException e){
            throw new DaoException(e);
        }finally{
            pool.freeConnection(c);
        }
    }
    
}
